/**
 * 将deputy_general_base表的py_name字段重命名为head_img
 */

const mysql = require('mysql2/promise');

// 数据库连接配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

class FieldRenamer {
    constructor() {
        this.connection = null;
    }

    // 连接数据库
    async connect() {
        try {
            this.connection = await mysql.createConnection(dbConfig);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    // 断开数据库连接
    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已断开');
        }
    }

    // 检查字段是否存在
    async checkFieldExists(tableName, fieldName) {
        try {
            const [columns] = await this.connection.execute(`
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = 'nextjs' 
                AND TABLE_NAME = ? 
                AND COLUMN_NAME = ?
            `, [tableName, fieldName]);

            return columns.length > 0;
        } catch (error) {
            console.error(`❌ 检查字段${fieldName}失败:`, error.message);
            throw error;
        }
    }

    // 重命名字段
    async renameField() {
        try {
            console.log('🔧 开始重命名字段...');

            // 检查py_name字段是否存在
            const pyNameExists = await this.checkFieldExists('deputy_general_base', 'py_name');
            if (!pyNameExists) {
                console.log('ℹ️ py_name字段不存在，跳过重命名');
                return;
            }

            // 检查head_img字段是否已存在
            const headImgExists = await this.checkFieldExists('deputy_general_base', 'head_img');
            if (headImgExists) {
                console.log('ℹ️ head_img字段已存在，跳过重命名');
                return;
            }

            // 重命名字段
            await this.connection.execute(`
                ALTER TABLE deputy_general_base 
                CHANGE COLUMN py_name head_img VARCHAR(100) NULL COMMENT '头像图片'
            `);

            console.log('✅ 字段重命名成功: py_name -> head_img');

        } catch (error) {
            console.error('❌ 重命名字段失败:', error.message);
            throw error;
        }
    }

    // 验证重命名结果
    async verifyRename() {
        try {
            console.log('🔍 开始验证重命名结果...');

            // 检查字段是否存在
            const pyNameExists = await this.checkFieldExists('deputy_general_base', 'py_name');
            const headImgExists = await this.checkFieldExists('deputy_general_base', 'head_img');

            console.log(`📊 字段检查结果:`);
            console.log(`   py_name字段存在: ${pyNameExists ? '是' : '否'}`);
            console.log(`   head_img字段存在: ${headImgExists ? '是' : '否'}`);

            if (headImgExists) {
                // 显示字段详细信息
                const [fieldInfo] = await this.connection.execute(`
                    SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_SCHEMA = 'nextjs' 
                    AND TABLE_NAME = 'deputy_general_base' 
                    AND COLUMN_NAME = 'head_img'
                `);

                if (fieldInfo.length > 0) {
                    const field = fieldInfo[0];
                    console.log('📋 head_img字段详细信息:');
                    console.log(`   字段名: ${field.COLUMN_NAME}`);
                    console.log(`   数据类型: ${field.DATA_TYPE}`);
                    console.log(`   允许空值: ${field.IS_NULLABLE}`);
                    console.log(`   默认值: ${field.COLUMN_DEFAULT || 'NULL'}`);
                    console.log(`   注释: ${field.COLUMN_COMMENT || '无'}`);
                }

                // 显示一些示例数据
                const [samples] = await this.connection.execute(`
                    SELECT id, name, head_img, star_level, sex 
                    FROM deputy_general_base 
                    WHERE head_img IS NOT NULL 
                    ORDER BY RAND() 
                    LIMIT 10
                `);

                console.log('📋 示例数据:');
                samples.forEach(sample => {
                    const gender = sample.sex === 1 ? '男' : '女';
                    console.log(`   ID:${sample.id} ${sample.name} - 头像:${sample.head_img} 星级:${sample.star_level} 性别:${gender}`);
                });
            }

        } catch (error) {
            console.error('❌ 验证重命名结果失败:', error.message);
            throw error;
        }
    }

    // 运行完整流程
    async run() {
        try {
            console.log('🚀 开始重命名字段: py_name -> head_img...\n');

            // 1. 连接数据库
            await this.connect();

            // 2. 重命名字段
            await this.renameField();

            // 3. 验证重命名结果
            await this.verifyRename();

            console.log('\n🎉 字段重命名完成！');

        } catch (error) {
            console.error('💥 操作失败:', error.message);
            throw error;
        } finally {
            // 4. 断开连接
            await this.disconnect();
        }
    }
}

// 运行脚本
async function main() {
    const renamer = new FieldRenamer();
    await renamer.run();
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = FieldRenamer;

